<?php
class DataBaseProvider
{
    static function getAllImages()
    {
        $query = 'SELECT file_name FROM uploads WHERE file_name LIKE "%.jpg" ' .
                 'OR file_name LIKE "%.gif" OR file_name LIKE "%.png"';
        return $query;
    }
    
    static function getFileList()
    {
        $query = 'SELECT up.file_name, up.rating, up.user_id, us.first_name, us.last_name ' .
                 'FROM uploads AS up, users AS us ' .
                 'WHERE us.user_id = up.user_id';
        
        if ( isset( $_GET['extension'] ) || isset( $_GET['email'] ) )
        {         
            $isBothParamExist = isset( $_GET['extension'] ) && !empty( $_GET['extension'] );
            if ( $isBothParamExist )
            {
                $query .= ' AND up.file_name LIKE "%' . DataBase::getInstance()->quote( $_GET['extension'] ) . '"';
            }
            if ( isset( $_GET['email'] ) && !empty( $_GET['email'] ) )
            {
                $email = DataBase::getInstance()->quote( strtolower( $_GET['email'] ) );
                $userId = DataBase::getInstance()->query( 'SELECT user_id FROM users WHERE email = "' . $email . '";' );
                if ( !empty( $userId ) )
                {
                    $query .= ' AND us.user_id = ' . $userId[0]['user_id'];
                }
                else
                {
                    return NULL;
                }
            }
        }
        $query .= ';';
        return $query;
    }
    
    static function deleteRatingRecord( $lastId )
    {
        $query = 'DELETE FROM rating_log WHERE record_id = ' . $lastId . ';';
        return $query;
    }
    
    static function selectRatingByFileName( $fileName )
    {
        $query = 'SELECT rating FROM uploads WHERE file_name = "' . $fileName . '";';
        return $query;
    }
    
    static function insertRecordIntoRatingLog( $fileName, $changeCommand )
    {
        $query = 'INSERT INTO rating_log (file_name, user_login, rating_change) ' .
                 "VALUES ('$fileName', '" . $_SESSION['login'] . "', '$changeCommand');";
        return $query;
    }
    
    static function isAlreadyVote( $fileName )
    {
        $query = 'SELECT * FROM rating_log WHERE user_login = "' . $_SESSION['login'] . '" ' .
                 'AND file_name = "' . $fileName . '";';
        return $query;
    }
    
    static function updateRatingQuery( $currentRating, $fileName )
    {
        $query = 'UPDATE uploads SET rating = ' . $currentRating . ' WHERE file_name = "' . $fileName . '";';
        return $query;
    }
    
    static function selectUserById( $email )
    {
        $query = 'SELECT user_id FROM users WHERE email = \'' . $email . '\';';
        return $query;
    }
    
    static function isFileAlreadyUploaded( $fileName )
    {
        $query = 'SELECT upload_id FROM uploads WHERE file_name = "' . $fileName . '";';
        return $query;
    }
    
    static function insertUploadRecordIntoDb( $fileName, $userId )
    {
        $query = 'INSERT INTO uploads (file_name, user_id) VALUES (\'' . $fileName . '\', \'' . $userId . '\');';
        return $query;
    }
    
    static function usersRangeQuery( $start )
    {
        $query = 'SELECT user_id, first_name, last_name, email FROM users LIMIT ' . $start . ',' . USERS_ON_PAGE . ';';
        return $query;
    }
    
    static function countUsers()
    {
        $query = 'SELECT COUNT(*) FROM users;';
        return $query;
    }
    
    static function generateSaveUserQuery( $firstName, $lastName, $email, $password, $sex, $birthday )
    {
        $query = "INSERT INTO users (first_name, last_name, email, pass, sex, birthday) VALUES " .
                 "('$firstName', '$lastName', '$email', '$password', '$sex', '$birthday');";
        return $query;
    }
    
    static function getUserInfoByEmail( $email )
    {
        $query = 'SELECT * FROM users WHERE email = \'' . $email . '\';';
        return $query;
    }
    
    static function generateFindUserQuery( $email, $password )
    {
        $email = DataBase::getInstance()->quote( $email );
        $query = 'SELECT * FROM users WHERE email = \'' . $email . '\'';
        if ( $password )
        {
            $password = DataBase::getInstance()->quote( $password );
            $query   .= ' AND pass = \'' . $password . '\'';
        }
        $query .= ';';
        return $query;
    }
    
    static function getUserById( $id )
    {
        $query = 'SELECT * FROM users WHERE user_id = ' . $id . ';';
        return $query;
    }
}
?>